package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import ModelLayer.*;

public class DBPartOrder implements IFDBPartOrder {
	
	private  Connection con;
    /** Creates a new instance of DBProduct */
    public DBPartOrder() {
      con = DbConnection1.getInstance().getDBcon();
    }


	@Override
	public ArrayList<PartOrder> getAllPartOrders(boolean retrieveAssociation) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public PartOrder findPartOrder(int partOrderID, boolean retrieveAssociation) {
		// TODO Auto-generated method stub
		return null;
	}

	public int insertPartOrder(PartOrder pOrd, Order ord) throws Exception {
	//  Auto-generated method stub
				//call to get the next ID number
		       int rc = -1;
			   String query="INSERT INTO productsalesorder(psorderid, psprodid, psamount, psdeliverystatus, psdeliverydate)  VALUES('"+
			  		//pOrd.getOrder().getOrderId() + "','" +
			  		ord.getOrderId() + "','" +
					pOrd.getProduct().getProductId()  + "','"  +
				    pOrd.getAmount() + "','"  +
		            pOrd.getDeliveryStatus() + "','"  +
		            pOrd.getDeliveryDate() + "')";

		       System.out.println("insert : " + query);
		      try{ // insert new clothing
		          Statement stmt = con.createStatement();
		          stmt.setQueryTimeout(5);
		     	  rc = stmt.executeUpdate(query);
		          stmt.close();
		      }//end try
		       catch(SQLException ex){
		          System.out.println("PartOrder ikke oprettet");
		          throw new Exception ("PartOrder is not inserted correct");
		       }
		       return(rc);

		}


	@Override
	public int updatePartOrder(PartOrder pOrd) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public int deletePartOrder(int pOrderID) {
		// TODO Auto-generated method stub
		return 0;
	}
	
	private PartOrder singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        PartOrder ordObj = new PartOrder();
        String query =  buildQuery(wClause);
        System.out.println("DbPartOrder -singleWhere " + query); 
        try{ // read from Product
        	Statement stmt = con.createStatement();
        	stmt.setQueryTimeout(5);
        	results = stmt.executeQuery(query);

	   if( results.next() ){
	       ordObj = buildPartOrder(results);
               //missing the test on retriveassociation
               
           }//end if
	   stmt.close();
	   if(retrieveAssociation)
       {   //The Invoice, Employee and Customer is to be build as well
		   int productID = ordObj.getProduct().getProductId();
           DBProduct dbProduct= new DBProduct();
           Product prod = dbProduct.findProduct(productID, true);
           ordObj.setProduct(prod);
           System.out.println("Product is seleceted");
           int orderId = ordObj.getOrder().getOrderId();
           DBOrder dbOrder = new DBOrder();
           Order ord = dbOrder.findOrder(orderId, true);
           ordObj.setOrder(ord);
           System.out.println("Order selected");
          
       
	}//slut try	
        else{ //no order was found
            ordObj = null;
        }
        }
	catch(Exception e){
	   System.out.println("Query exception - select PartOrder : "+e);
	   e.printStackTrace();
        }
	return ordObj;
    }
	
	 //miscWhere is used when more than one Product is selected and build
    private ArrayList<PartOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<PartOrder> list = new ArrayList<PartOrder>();
		
		String query =  buildQuery(wClause);
                System.out.println("DbPartOrder " + query); 
  		try{ // read from product
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 	
			while( results.next() ){
				PartOrder ordObj = new PartOrder();
			 ordObj = buildPartOrder(results);	
                         list.add(ordObj);
                         //missing tes on retriveAssociation
			}//end while
			stmt.close();
			if(retrieveAssociation)
            {   //The supervisor and department is to be build as well
                for(PartOrder ordObj : list){
                	int productID = ordObj.getProduct().getProductId();
                    DBProduct dbProduct= new DBProduct();
                    Product prod = dbProduct.findProduct(productID, true);
                    ordObj.setProduct(prod);
                    System.out.println("Product is seleceted");
                    int orderId = ordObj.getOrder().getOrderId();
                    DBOrder dbOrder = new DBOrder();
                    Order ord = dbOrder.findOrder(orderId, true);
                    ordObj.setOrder(ord);
                    System.out.println("Order selected");
                }
            }//end if   
		
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select partorder : "+e);
			e.printStackTrace();
	 	}
		return list;
	}
    
    private PartOrder buildPartOrder(ResultSet results)
    {
    	PartOrder pordObj = new PartOrder();
    	Order ordObj = new Order();
    	Product prodObj = new Product();
       
         try{
               ordObj.setOrderId(results.getInt("psorderid"));
               pordObj.setOrder(ordObj);
               prodObj.setProductId(results.getInt("psprodid"));
               pordObj.setProduct(prodObj);
               pordObj.setAmount(results.getInt("psAmount"));
               pordObj.setDeliveryStatus(results.getString("psDeliveryStatus"));
               pordObj.setDeliveryDate(results.getString("psDeliveryDate"));
          }
         catch(Exception e)
         {
             System.out.println("building PartOrder object");
         }
        
        return pordObj;
        
    }
    
    private String buildQuery(String wClause)
	{
            String query="SELECT psorderid, psprodid, psAmount, psDeliveryStatus, psDeliveryDate FROM productsalesorder, salesorder, product where psorderid=salesorderid and psprodid=productid "; 
            
           

		if (wClause.length()>0)
			query = query + "  and " + wClause;
			
		return query;
	}
}
